-- 检查字符串中，第 n 次出现 “;” 号的行号
drop function if exists find_nth_semicolon_line_f;
CREATE OR REPLACE FUNCTION find_nth_semicolon_line_f(
    input_text TEXT,
    n bigint
) RETURNS bigint AS $$
DECLARE
    lines TEXT[];
    current_line TEXT;
    line_number bigint := 1;
    total_semicolons bigint := 0;
    line_semicolons bigint;
BEGIN
    -- 处理空输入
    IF input_text IS NULL OR n <= 0 THEN
        RETURN NULL;
    END IF;

    -- 标准化换行符并分割文本
    lines := regexp_split_to_array(
        regexp_replace(input_text, E'\r?\n', E'\n', 'g'),
        '\n'
    );

    -- 逐行处理
    FOR i IN 1..array_length(lines, 1) LOOP
        current_line := lines[i];
        -- 计算当前行的分号数量
        line_semicolons := length(current_line) - length(replace(current_line, ';', ''));

        -- 检查是否达到目标分号
        IF total_semicolons + line_semicolons >= n THEN
            RETURN line_number;
        END IF;

        -- 更新计数器
        total_semicolons := total_semicolons + line_semicolons;
        line_number := line_number + 1;
    END LOOP;

    -- 如果未找到足够的分号
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;


SELECT find_nth_semicolon_line(
'asdf;
asdf;
asdf
sadf
atthasdeffdsa;
asdf;
fasdf
ffff;
'
, 3) AS result_line

select unnest(
regexp_split_to_array(
        regexp_replace(
-- input_text
'asdf;
asdf;
asdf
sadf
atthasdeffdsa;
asdf;
fasdf
ffff;
'
, E'\r?\n', E'\n', 'g'),
        '\n'
    ));

select unnest( regexp_split_to_array(
trim(REPLACE(
'asdf;
asdf;
asdf
sadf
atthasdeffdsa;
asdf;
fasdf
ffff;
'
, E'\r', ''))
,'\n'));